<!DOCTYPE html>
<html>
<head>
    <title>004.PHP与sql server连接</title>

    <meta charset="UTF-8"/>

    <meta content="text/html; charset=utf-8" http-equiv="Content-Type"/>

    <meta name="HandheldFriendly" content="true"/>

    <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no"/>

    <meta name="apple-mobile-web-app-capable" content="yes"/>

    <meta name="apple-mobile-web-app-status-bar-style" content="black"/>

    <link href="./asset/farbox/basic.css" type="text/css" rel="stylesheet"/>
    <link href="./asset/farbox/main.css" type="text/css" rel="stylesheet"/>

    <style type="text/css">
        
            span.md_line{margin-bottom:0.5em; display:block; line-height:1.89}
            .md_line br{ display: none;}
            
    </style>

    <script>
        var flowchat_options = {
            'x': 0, 'y': 0, 'line-width': 1, 'line-length': 50, 'text-margin': 10, 'font-size': 13,
            'font-color': '#3c3c3c', 'line-color': '#666666', 'element-color': '#666666', 'fill': 'transparent',
            'yes-text': 'yes', 'no-text': 'no', 'arrow-end': 'block', 'class': 'flowchart', 'scale': 1,
            'symbols': { 'start': {}, 'end': {}, 'condition': {}, 'inputoutput': {}, 'operation': {}, 'subroutine': {}}
        }
    </script>
    <script type="text/javascript" src="./asset/other/raphael-min.js"></script>
    <script type="text/javascript" src="./asset/other/flowchart.js"></script>
    <script type="text/javascript" src="./asset/other/echarts.min.js"></script>


</head>

<body>
    
        <a href="./index.html" id="go_to_home"> &lt; home </a>
    
    <div id="sidebar">
        <ul id="sidebar_body">
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./001.软件下载安装配置.html" class="">第一章 软件下载安装配置</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./002.数据库操作.html" class="">002.数据库操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./003.数据表操作.html" class="">003.数据表操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./004.PHP与sql server连接.html" class="selected">004.PHP与sql server连接</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./005.数据的增删改.html" class="">005.数据的增删改</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./006.数据操作-简单查询.html" class="">006.数据操作-简单查询</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./007.高级查询.html" class="">07.高级查询-排序 多表联合 聚合与分组 限制条数</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./008.多表连接查询.html" class="">08.多表连接查询</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./009.子查询 ANY ALL [NOT] IN [NOT]EXISTS.html" class="">09.子查询 ANY ALL [NOT] IN [NOT]EXISTS</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./010.事务-索引-视图.html" class="">010.事务-索引-视图</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./011.T-SQL.html" class="">011.T-SQL学习</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./012.存储过程.html" class="">012.存储过程</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./013.触发器.html" class="">10.触发器</a>
                </li>
            

            
        
        </ul>
    </div>

    <div id="main">
        <div class="content_body">
            <h1 class="title"> 004.PHP与sql server连接 </h1>
            <div class="doc_post"> <p class="md_block">

<div class="toc"><ul>
<li>
<a href="#toc_0">004.PHP与sql server连接</a>
<ul>
<li>
<a href="#toc_1">1、下载驱动文件</a>
</li>
<li>
<a href="#toc_2">2.配置PHP的扩展,让PHP支持sqlsrv与pdo_sqlsrv驱动</a>
</li>
<li>
<a href="#toc_3">在WINDOWS下安装ODBC驱动:msodbcsql(11_x64).msi</a>
</li>
<li>
<a href="#toc_4">测试连接</a>
</li>
</ul>
</li>
</ul>
</div>
</p>


<h2 id="toc_1">1、下载驱动文件</h2>

<p class="md_block">
    <span class="md_line">在微软官网找到了:<br /></span>
    <span class="md_line">Microsoft Drivers for PHP for SQL Server<br /></span>
    <span class="md_line md_line_dom_embed"><a class="md_compiled" href="http://www.microsoft.com/en-us/download/details.aspx?id=20098">点击去下载:http://www.microsoft.com/en-us/download/details.aspx?id=20098</a></span>
</p>


<p class="md_block">
    <span class="md_line">下载链接地址有四个文件：<br /></span>
    <span class="md_line">•SQLSRV30.EXE<br /></span>
    <span class="md_line">•SQLSRV31.EXE<br /></span>
    <span class="md_line">•SQLSRV32.EXE<br /></span>
    <span class="md_line">•SQLSRV40.EXE<br /></span>
    <span class="md_line">分别支持不同的PHp版本<br /></span>
    <span class="md_line">•Version 4.0 supports PHP 7.0+<br /></span>
    <span class="md_line">•Version 3.2 supports PHP 5.6, 5.5, and 5.4<br /></span>
    <span class="md_line">•Version 3.1 supports PHP 5.5 and 5.4<br /></span>
    <span class="md_line">•Version 3.0 supports PHP 5.4.<br /></span>
    <span class="md_line">如果是7.1或以上版本呢?<br /></span>
    <span class="md_line">我们可以下载微软非官方的驱动<br /></span>
    <span class="md_line md_line_dom_embed"><a class="md_compiled" href="https://github.com/microsoft/msphpsql/tags">https://github.com/microsoft/msphpsql/tags</a></span>
</p>


<h2 id="toc_2">2.配置PHP的扩展,让PHP支持sqlsrv与pdo_sqlsrv驱动</h2>

<p class="md_block md_has_block_below md_has_block_below_ol">
    <span class="md_line">我们用的是PHP5.6版本,操作如下</span>
</p>


<ol>
<li>
<p class="md_block">
    <span class="md_line">因为我安装的是PHP5.6版本，所以下载的是SQLSRV32.EXE，选择解压路径为：D:\软件\PHP\连接sqlserver驱动库\SQLSRV32-要解压-支持5.4-5.5-5.6。</span>
</p></li>
<li>
<p class="md_block">
    <span class="md_line">因为我们用的PHP是NTS(线程非安全)版本,所以复制刚才解压目录中的:php_sqlsrv_56_nts.dll 与 php_pdo_sqlsrv_56_nts.dll 两个文件到 PHP扩展目录(D:\phpStudy\php\php-5.6.27-nts\ext)中</span>
</p></li>
</ol>
<img onerror="this.src='../_image/2019-11-08-09-34-42.jpg';this.onerror=null;"  class="" src="/_image/2019-11-08-09-34-42.jpg" alt="" title="" >
复制两文件到:
<img onerror="this.src='../_image/2019-11-08-09-36-45.jpg';this.onerror=null;"  class="" src="/_image/2019-11-08-09-36-45.jpg" alt="" title="" >
<ol>
<li>修改PHP的配置文件php.ini (D:\phpStudy\php\php-5.6.27-nts\php.ini)文件,</li>
</ol>
<img onerror="this.src='../_image/2019-11-08-09-38-19.jpg';this.onerror=null;"  class="" src="/_image/2019-11-08-09-38-19.jpg" alt="" title="" >
<ol>
<li>完成后重启Apache,查看PHPINFO();搜索sqlsrv,找到如下扩展信息则安装驱动成功.</li>
</ol>
<img onerror="this.src='../_image/2019-11-08-09-40-48.jpg';this.onerror=null;"  class="" src="/_image/2019-11-08-09-40-48.jpg" alt="" title="" >
<img onerror="this.src='../_image/2019-11-08-09-41-12.jpg';this.onerror=null;"  class="" src="/_image/2019-11-08-09-41-12.jpg" alt="" title="" >
<h2 id="toc_3">在WINDOWS下安装ODBC驱动:msodbcsql(11_x64).msi</h2>

<p class="md_block">
    <span class="md_line md_line_dom_embed"><a class="md_compiled" href="https://download.microsoft.com/download/8/4/D/84DD73BF-93E9-4298-B54D-CB779F9D0EC9/CHS/x64/msodbcsql.msi">下载:https://download.microsoft.com/download/8/4/D/84DD73BF-93E9-4298-B54D-CB779F9D0EC9/CHS/x64/msodbcsql.msi</a></span>
</p>


<h2 id="toc_4">测试连接</h2>
<div class="codehilite code_lang_php  highlight"><pre><span></span><span class="nb">header</span><span class="p">(</span><span class="s1">&#39;Content-Type:text/html;charset=gbk&#39;</span><span class="p">);</span>


<span class="nv">$server_ip</span> <span class="o">=</span> <span class="s1">&#39;127.0.0.1&#39;</span><span class="p">;</span>
<span class="nv">$conn_info</span> <span class="o">=</span> <span class="p">[</span><span class="s1">&#39;database&#39;</span><span class="o">=&gt;</span><span class="s1">&#39;master&#39;</span><span class="p">,</span><span class="s1">&#39;UID&#39;</span><span class="o">=&gt;</span><span class="s1">&#39;sa&#39;</span><span class="p">,</span><span class="s1">&#39;PWD&#39;</span><span class="o">=&gt;</span><span class="s1">&#39;123456&#39;</span><span class="p">];</span>

<span class="c1">//1.连接到数据库服务器,需要指定IP,数据库名,用户名,密码</span>
<span class="c1">//sqlsrv_connect(&#39;127.0.0.1,1433&#39;,[&#39;Database&#39;=&gt;&#39;master&#39;,&#39;UID&#39;=&gt;&#39;sa&#39;,&#39;PWD&#39;=&gt;&#39;123456&#39;])</span>
<span class="nv">$conn</span> <span class="o">=</span> <span class="nb">sqlsrv_connect</span><span class="p">(</span><span class="nv">$server_ip</span><span class="p">,</span><span class="nv">$conn_info</span><span class="p">);</span>
<span class="c1">//2.检测连接是否成功,不成功则输出错误信息</span>
<span class="k">if</span><span class="p">(</span><span class="nv">$conn</span> <span class="o">===</span> <span class="k">FALSE</span><span class="p">){</span>
    <span class="nb">var_dump</span><span class="p">(</span><span class="nb">sqlsrv_errors</span><span class="p">());</span>
    <span class="k">die</span><span class="p">();</span><span class="c1">//退出程序</span>
<span class="p">}</span><span class="k">else</span><span class="p">{</span>
    <span class="k">echo</span> <span class="nb">iconv</span><span class="p">(</span><span class="s1">&#39;utf-8&#39;</span><span class="p">,</span><span class="s1">&#39;gbk&#39;</span><span class="p">,</span><span class="s1">&#39;连接成功!&lt;br /&gt;&#39;</span><span class="p">);</span>
<span class="p">}</span>
<span class="c1">//在打开的数据库连接中执行SQL语句.</span>
<span class="c1">//1. 创建数据库</span>
<span class="nv">$sql</span> <span class="o">=</span> <span class="s1">&#39;CREATE DATABASE kp11b&#39;</span><span class="p">;</span>
<span class="nv">$stmt</span> <span class="o">=</span> <span class="nb">sqlsrv_query</span><span class="p">(</span><span class="nv">$conn</span><span class="p">,</span><span class="nv">$sql</span><span class="p">);</span>
<span class="k">if</span><span class="p">(</span><span class="nv">$stmt</span> <span class="o">===</span> <span class="k">FALSE</span><span class="p">){</span>
    <span class="nb">var_dump</span><span class="p">(</span><span class="nb">sqlsrv_errors</span><span class="p">());</span>
    <span class="k">die</span><span class="p">();</span><span class="c1">//退出程序</span>
<span class="p">}</span><span class="k">else</span><span class="p">{</span>
    <span class="k">echo</span> <span class="nb">iconv</span><span class="p">(</span><span class="s1">&#39;utf-8&#39;</span><span class="p">,</span><span class="s1">&#39;gbk&#39;</span><span class="p">,</span><span class="s1">&#39;执行SQL语句成功:创建数据库!&lt;br /&gt;&#39;</span><span class="p">);</span>
<span class="p">}</span>

<span class="c1">//2.执行SQL语句,连接到刚建好的数据库上</span>
<span class="nv">$sql</span> <span class="o">=</span> <span class="s1">&#39;USE kp11b&#39;</span><span class="p">;</span>
<span class="nv">$stmt</span> <span class="o">=</span> <span class="nb">sqlsrv_query</span><span class="p">(</span><span class="nv">$conn</span><span class="p">,</span><span class="nv">$sql</span><span class="p">);</span>
<span class="k">if</span><span class="p">(</span><span class="nv">$stmt</span> <span class="o">===</span> <span class="k">FALSE</span><span class="p">){</span>
    <span class="nb">var_dump</span><span class="p">(</span><span class="nb">sqlsrv_errors</span><span class="p">());</span>
    <span class="k">die</span><span class="p">();</span><span class="c1">//退出程序</span>
<span class="p">}</span><span class="k">else</span><span class="p">{</span>
    <span class="k">echo</span> <span class="nb">iconv</span><span class="p">(</span><span class="s1">&#39;utf-8&#39;</span><span class="p">,</span><span class="s1">&#39;gbk&#39;</span><span class="p">,</span><span class="s1">&#39;执行SQL语句成功:选择数据库!&lt;br /&gt;&#39;</span><span class="p">);</span>
<span class="p">}</span>
<span class="c1">//3.建表</span>
<span class="nv">$sql</span> <span class="o">=</span> <span class="s1">&#39;CREATE TABLE stu_info(id int primary key identity,uname varchar(50) NOT NULL,age tinyint,sex bit default 0,addr nvarchar(300))&#39;</span><span class="p">;</span>
<span class="nv">$stmt</span> <span class="o">=</span> <span class="nb">sqlsrv_query</span><span class="p">(</span><span class="nv">$conn</span><span class="p">,</span><span class="nv">$sql</span><span class="p">);</span>
<span class="k">if</span><span class="p">(</span><span class="nv">$stmt</span> <span class="o">===</span> <span class="k">FALSE</span><span class="p">){</span>
    <span class="nb">var_dump</span><span class="p">(</span><span class="nb">sqlsrv_errors</span><span class="p">());</span>
    <span class="k">die</span><span class="p">();</span><span class="c1">//退出程序</span>
<span class="p">}</span><span class="k">else</span><span class="p">{</span>
    <span class="k">echo</span> <span class="nb">iconv</span><span class="p">(</span><span class="s1">&#39;utf-8&#39;</span><span class="p">,</span><span class="s1">&#39;gbk&#39;</span><span class="p">,</span><span class="s1">&#39;执行SQL语句成功:创建表成功!&lt;br /&gt;&#39;</span><span class="p">);</span>
<span class="p">}</span>

<span class="c1">//资源只要打开了,就要记得释放.</span>
<span class="nb">sqlsrv_free_stmt</span><span class="p">(</span><span class="nv">$stmt</span><span class="p">);</span>
<span class="nb">sqlsrv_close</span><span class="p">(</span><span class="nv">$conn</span><span class="p">);</span>
</pre></div>

<!--block_code_end--> </div>
        </div>

    </div>





<script>
    // auto focus for menu
    window.onload = function(){
        var sidebar = document.getElementById('sidebar');
        var current = document.getElementsByClassName('selected')[0];
        if (current && sidebar){
            sidebar.scrollTop = current.offsetTop - sidebar.clientHeight/2 + 100
        }
    };
</script>




</body>

</html>